package com.example.demo.Utils;

import com.example.demo.Bean.UserTable;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Component
public class SqlUtil {

    @Value("${server-mysql-driver}")
    public  String serverMysqlDriver;
    @Value("${server-mysql-url}")
    public  String serverMysqlUrl;
    @Value("${server-mysql-username}")
    public  String serverMysqlUsername;
    @Value("${server-mysql-password}")
    public  String serverMysqlPassword;

    private  Connection rootConnection;

    private  PreparedStatement rootPreparedStatement;

    private Map<String,Connection> connectionMap;

    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    @PostConstruct
    private void init(){
        try{
            connectionMap = new HashMap<>();
            Class.forName(serverMysqlDriver);
            rootConnection = DriverManager.getConnection(serverMysqlUrl,serverMysqlUsername,serverMysqlPassword);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void connect(String studentUid) throws Exception{
        try {
            Connection connection = connectionMap.get(studentUid);
            if (connection != null) {
                if (!connection.isValid(3000)) {
                    connectionMap.put(studentUid, DriverManager.getConnection(serverMysqlUrl, studentUid, studentUid));
                }
            } else {
                if (!connectionMap.containsKey(studentUid)) {
                    initUserPlace(studentUid);
                }
                connectionMap.put(studentUid, DriverManager.getConnection(serverMysqlUrl, studentUid, studentUid));
                System.out.println(connectionMap.get(studentUid)==null);
            }
            enterDataBase(connectionMap.get(studentUid), studentUid);
            System.out.println(connectionMap.get(studentUid)==null);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
    }

    public ResultSet sql(String sql,String studentUid) throws Exception{
        ResultSet resultSet = null;
        try {
            Connection connection = connectionMap.get(studentUid);
            Statement statement = connection.createStatement();
            boolean isResultSet = statement.execute(sql);
            if (isResultSet) {
                resultSet = statement.getResultSet();
            }
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        return resultSet;
    }

    public void closeConnection(String studentUid) throws Exception{
        Connection connection = connectionMap.get(studentUid);
        connection.close();
       // connectionMap.remove(studentUid);
    }

    public void deleteUserPlace(String studentUid) throws Exception{
        if(connectionMap.get(studentUid) != null) {
            rootPreparedStatement = rootConnection.prepareStatement("drop user ?@'%'");
            rootPreparedStatement.setObject(1, studentUid);
            rootPreparedStatement.executeUpdate();
            rootPreparedStatement = rootConnection.prepareStatement("drop database _" + studentUid);
            rootPreparedStatement.executeUpdate();
            Connection connection = connectionMap.get(studentUid);
            connection.close();
            connectionMap.remove(studentUid);
        }
    }

    public UserTable packResultSet(ResultSet resultSet) throws Exception{
        UserTable resultMap = new UserTable();
        ResultSetMetaData metaData = resultSet.getMetaData();
        HashMap<Integer,String> columnName = new HashMap<>();
        for(int i = 1 ; i <= metaData.getColumnCount() ; i++){
           columnName.put(i,metaData.getColumnLabel(i));
        }
        resultMap.setColumnName(columnName);
        ArrayList<Map<String,Object>> tableData = new ArrayList<>();
        while (resultSet.next()){
            HashMap<String,Object> map = new HashMap<>();
            for(int i = 1 ; i <= metaData.getColumnCount() ; i++){
                Object o = resultSet.getObject(i);
                if (o instanceof Boolean){
                    Boolean b = (Boolean)o;
                    o = b ? 1 : 0;
                }
                if(o instanceof Date){
                   Date d = (Date)o;
                   o = dateFormat.format(d);
                }
              map.put(metaData.getColumnLabel(i),o);
            }
            tableData.add(map);
        }
        resultMap.setTableData(tableData);
        return resultMap;
    }

    private void initUserPlace(String studentUid) throws Exception{
        if (!rootConnection.isValid(3000)) {
            rootConnection = DriverManager.getConnection(serverMysqlUrl,serverMysqlUsername,serverMysqlPassword);
        }
        createMysqlUser(studentUid);
        createMysqlDatabase(studentUid);
        grantPrivileges(studentUid);
    }


    private void createMysqlUser(String studentUid) throws Exception{
        rootPreparedStatement = rootConnection.prepareStatement("create user ?@'%' identified by ?");
        rootPreparedStatement.setObject(1,studentUid);
        rootPreparedStatement.setObject(2,studentUid);
        rootPreparedStatement.executeUpdate();
    }

    private void enterDataBase(Connection connection, String studentUid) throws Exception{
        Statement statement = connection.createStatement();
        statement.execute("use _" + studentUid);
        statement.close();
    }

    private void createMysqlDatabase(String studentUid) throws Exception{
        String sql = "create database _"+studentUid;
        rootPreparedStatement = rootConnection.prepareStatement(sql);
        rootPreparedStatement.executeUpdate();
    }

    private void grantPrivileges(String studentUid) throws Exception{
        rootPreparedStatement = rootConnection.prepareStatement("grant all on _"+studentUid+".* to ?@'%'");
        rootPreparedStatement.setObject(1,studentUid);
        rootPreparedStatement.executeUpdate();
    }

}
